package com.ph.irp.stock.dao.impl;

import com.ph.util.DBUtil;
import com.ph.util.DateUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.model.PStockInStorage;
import com.ph.irp.stock.dao.StockInStorageDAO;

public class StockInStorageDAOImpl implements StockInStorageDAO {

	/**
	 * 
	 */
	private static final long serialVersionUID = -99909740457039717L;
	private ComboPooledDataSource dataSource;

	public ComboPooledDataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public void addStockInStorage(PStockInStorage stockInStorage)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append("insert into p_stock_stockinstorage");
		sb
				.append("(SupplierID, InStorageDate, InStorageCode, SourceBillType, StockWay, departmentID,");
		sb
				.append("ProtectorID, ManagerID, AcceptancerID, BusinesserID, MakeBiller, Auditinger,");
		sb
				.append("AuditingDate, BookKeeper, IsAuditing, IsCancel, IsClosed, IsHook, HookTag,");
		sb.append("CheckTag, tag, Remark) values(");
		sb.append(stockInStorage.getSupplierId());
		sb.append(", ");
		sb.append("'" + stockInStorage.getInStorageDate() + "'");
		sb.append(", ");
		sb.append("'" + stockInStorage.getInStorageCode() + "'");
		sb.append(", ");
		sb.append(stockInStorage.getSourceBillType());
		sb.append(", ");
		sb.append(stockInStorage.getStockWay());
		sb.append(", ");
		if (stockInStorage.getDepartmentId() == 0)
			sb.append("null");
		else
			sb.append(stockInStorage.getDepartmentId());
		sb.append(", ");
		if (stockInStorage.getProtectorId() == 0)
			sb.append("null");
		else
			sb.append(stockInStorage.getProtectorId());
		sb.append(", ");
		if (stockInStorage.getManagerId() == 0)
			sb.append("null");
		else
			sb.append(stockInStorage.getManagerId());
		sb.append(", ");
		if (stockInStorage.getAcceptancerId() == 0)
			sb.append("null");
		else
			sb.append(stockInStorage.getAcceptancerId());
		sb.append(", ");
		if (stockInStorage.getBusinesserId() == 0)
			sb.append("null");
		else
			sb.append(stockInStorage.getBusinesserId());
		sb.append(", ");
		if (stockInStorage.getMakeBiller() == null
				|| stockInStorage.getMakeBiller().trim().equals(""))
			sb.append("null");
		else
			sb.append("'" + stockInStorage.getMakeBiller() + "'");
		sb.append(", ");
		if (stockInStorage.getAuditinger() == null
				|| stockInStorage.getAuditinger().trim().equals(""))
			sb.append("null");
		else
			sb.append("'" + stockInStorage.getAuditinger() + "'");
		sb.append(", ");
		if (stockInStorage.getAuditingDate() == null)
			sb.append("null");
		else
			sb.append("'" + stockInStorage.getAuditingDate() + "'");
		sb.append(", ");
		if (stockInStorage.getBookKeeper() == null
				|| stockInStorage.getBookKeeper().trim().equals(""))
			sb.append("null");
		else
			sb.append("'" + stockInStorage.getBookKeeper() + "'");
		sb.append(", ");
		sb.append(stockInStorage.getIsAuditing());
		sb.append(", ");
		sb.append(stockInStorage.getIsCancel());
		sb.append(", ");
		sb.append(stockInStorage.getIsClosed());
		sb.append(", ");
		sb.append(stockInStorage.getIsHook());
		sb.append(", ");
		if (stockInStorage.getHookTag() == 0)
			sb.append("null");
		else
			sb.append(stockInStorage.getHookTag());
		sb.append(", ");
		if (stockInStorage.getCheckTag() == 0)
			sb.append("null");
		else
			sb.append(stockInStorage.getCheckTag());
		sb.append(", ");
		sb.append(stockInStorage.getTag());
		sb.append(", ");
		if (stockInStorage.getRemark() == null
				|| stockInStorage.getRemark().trim().equals(""))
			sb.append("null");
		else
			sb.append("'" + stockInStorage.getRemark() + "'");
		sb.append(")");
		// System.out.println(sb.toString());

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.execute();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

	}

	@Override
	public void deleteStockInStorage(String stockInStorageId) throws Throwable {
		
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb.append("delete from p_stock_stockinstorage where StockInStorageID = ?");
		
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.setInt(1, Integer.parseInt(stockInStorageId));
			pStmt.execute();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}

	@Override
	public List<PStockInStorage> getAllStockInStorages() throws Throwable {
		List<PStockInStorage> stockInStorages = null;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_stock_stockinstorage;");

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			stockInStorages = this.turnRSToList(rs);

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return stockInStorages;
	}

	@Override
	public PStockInStorage getStockInStorageById(int stockInStorageId)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_stock_stockinstorage where StockInStorageID = ?");
		PStockInStorage stockInStorage = null;
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.setInt(1, stockInStorageId);
			rs = pStmt.executeQuery();
			stockInStorage = turnRSToList(rs).get(0);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return stockInStorage;
	}

	@Override
	public int getStockInStorageIdByCode(String stockInStorageCode)
			throws Throwable {

		int inStorageId = 0;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb
				.append("select StockInStorageID from p_stock_stockinstorage where InStorageCode = ");
		sb.append("'" + stockInStorageCode + "';");

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			if (rs != null) {
				if (rs.next())
					inStorageId = rs.getInt("StockInStorageID");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return inStorageId;
	}

	@Override
	public void updateStockInStorage(PStockInStorage stockInStorage)
			throws Throwable {
		// TODO Auto-generated method stub

	}

	@Override
	public List<PStockInStorage> getStockInStorages(
			PStockInStorage stockInStorage, String beginDate, String endDate)
			throws Throwable {
		List<PStockInStorage> stockInStorages = new ArrayList<PStockInStorage>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_stock_stockinstorage where 1 = 1");
		if (stockInStorage != null) {
			if (stockInStorage.getSupplierId() != 0) {
				sb.append(" and SupplierID = ");
				sb.append(stockInStorage.getSupplierId());
			}
			if (stockInStorage.getInStorageCode() != null
					&& !stockInStorage.getInStorageCode().trim().equals("")) {
				sb.append(" and InStorageCode like");
				sb.append("'%" + stockInStorage.getInStorageCode() + "%'");
			}
			if (stockInStorage.getIsAuditing() != 2) {
				sb.append(" and IsAuditing = ");
				sb.append(stockInStorage.getIsAuditing());
			}
		}
		if (beginDate != null && !beginDate.trim().equals("")
				&& endDate != null && !endDate.trim().equals("")) {
			sb.append(" and InStorageDate between ");
			sb.append("'" + beginDate + "'");
			sb.append(" and ");
			sb.append("'" + endDate + "'");
		}
		sb.append(";");
		//System.out.println(sb.toString());

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			stockInStorages = turnRSToList(rs);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return stockInStorages;
	}

	public List<PStockInStorage> turnRSToList(ResultSet rs) throws Throwable {
		List<PStockInStorage> list = new ArrayList<PStockInStorage>();
		while (rs.next()) {
			PStockInStorage stockInStorage = new PStockInStorage();
			stockInStorage.setStockInStorageId(rs.getInt("StockInStorageID"));
			stockInStorage.setSupplierId(rs.getInt("SupplierID"));
			String ISDate = DateUtil.date2String(rs.getDate("InStorageDate"),
					DateUtil.YMD);
			if (ISDate.trim().equals("0000-00-00"))
				stockInStorage.setInStorageDate(null);
			else
				stockInStorage.setInStorageDate(ISDate);
			stockInStorage.setDepartmentId(rs.getInt("departmentID"));
			stockInStorage.setInStorageCode(rs.getString("InStorageCode"));
			stockInStorage.setSourceBillType(rs.getInt("SourceBillType"));
			stockInStorage.setStockWay(rs.getInt("StockWay"));
			stockInStorage.setProtectorId(rs.getInt("ProtectorID"));
			stockInStorage.setManagerId(rs.getInt("ManagerID"));
			stockInStorage.setAcceptancerId(rs.getInt("AcceptancerID"));
			stockInStorage.setBusinesserId(rs.getInt("BusinesserID"));
			stockInStorage.setMakeBiller(rs.getString("MakeBiller"));
			stockInStorage.setAuditinger(rs.getString("Auditinger"));
			Date AuDate = rs.getDate("AuditingDate");
			if (AuDate != null)
				stockInStorage.setAuditingDate(DateUtil.date2String(AuDate,
						DateUtil.YMD));
			else
				stockInStorage.setAuditingDate(null);
			stockInStorage.setBookKeeper(rs.getString("BookKeeper"));
			stockInStorage.setIsAuditing(rs.getInt("IsAuditing"));
			stockInStorage.setIsCancel(rs.getInt("IsCancel"));
			stockInStorage.setIsClosed(rs.getInt("IsClosed"));
			stockInStorage.setIsHook(rs.getInt("IsHook"));
			stockInStorage.setHookTag(rs.getInt("HookTag"));
			stockInStorage.setCheckTag(rs.getInt("CheckTag"));
			stockInStorage.setTag(rs.getInt("tag"));
			stockInStorage.setRemark(rs.getString("Remark"));
			list.add(stockInStorage);
		}
		return list;
	}

}
